package cn.edu.cug.cs.gtl.docdb.core;

import cn.edu.cug.cs.gtl.common.Identifier;
import cn.edu.cug.cs.gtl.io.Storable;
import cn.edu.cug.cs.gtl.protos.BoundingBox;
import cn.edu.cug.cs.gtl.protos.Document;
import cn.edu.cug.cs.gtl.protoswrapper.BoundingBoxWrapper;
import com.google.protobuf.ByteString;
import com.google.protobuf.InvalidProtocolBufferException;
import org.apache.commons.lang.StringUtils;

import java.io.*;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * 文档的元数据,数据表如下：采用PostgreSQL
 * internal_identifier     varchar(255)   primary key
 * title                   varchar(255)
 * version                 varchar(10)
 * type                    varchar(10)
 * keywords                varchar(255)
 * url                     varchar(255)
 * authors                 varchar(255)
 * affiliations            varchar(255)
 * abstract_text           varchar(1024)
 * content_text            varchar
 * schema                  varchar(1024)
 * bounding                varchar(255)
 * tags                    varchar(255)
 * comment                 varchar(1024)
 * catalog_identifier      varchar(255)
 * uuid                    varchar(255)
 * parent_identifier       varchar(255)
 * append_date             datetime
 * update_date             datetime
 * create_date             datetime
 * memo                    varchar(1024)
 */
public class DocMeta implements Storable {
    protected String title;
    protected List<String> keywords = new ArrayList<>();
    protected String version;
    protected String type;
    protected String url;
    protected List<String> authors = new ArrayList<>();
    protected List<String> affiliations = new ArrayList<>();
    protected String abstractText;
    protected String schema;//如果是数据表类的文档，这里记录相应的表结构
    protected String content;//文本内容
    protected BoundingBox bounding = null;//location
    protected List<String> tags = new ArrayList<>();//文档标签
    protected Identifier catalogIdentifier;//文档归类,指向一个归类对象的ID
    protected String uuid;//文档唯一编号
    protected Identifier parentIdentifier;
    protected Identifier internalIdentifier;//内部编号，供计算机使用
    protected String comment;//文档注释
    protected java.util.Date  appendDate;//YYYY-MM-DD hh:mm:ss
    protected java.util.Date  updateDate;//YYYY-MM-DD hh:mm:ss
    protected java.util.Date  createDate;//YYYY-MM-DD hh:mm:ss
    protected String memo;//文档备注

    /**
     * 从传入的Document对象中萃取元数据信息
     * @param document
     * @return
     */
    public static DocMeta create(Document document) throws ParseException {
        DocMeta docMeta = new DocMeta();
        if (document.getInternalId() != 0) {
            docMeta.setIdentifier(Identifier.create(document.getInternalId()));
        } else {
            docMeta.setIdentifier(Identifier.randomIdentifier());
        }
        if (document.getCatalog() != 0) {
            docMeta.catalogIdentifier = Identifier.create(document.getCatalog());
        } else {
            docMeta.catalogIdentifier = Identifier.create(-1);
        }
        docMeta.parentIdentifier = Identifier.create(-1);
        if (document.getBoundingCount() != 0) {
            docMeta.bounding = document.getBounding(0);
        }
        docMeta.title = document.getTitle();
        docMeta.memo = document.getMemo();
        docMeta.uuid = document.getUuid();
        docMeta.comment = document.getComment();
        docMeta.schema = document.getSchema();
        docMeta.type = document.getType();
        docMeta.version = document.getVersion();
        docMeta.url = document.getUrl();
        docMeta.content = document.getContentList().toString();
        docMeta.abstractText = document.getAbstract();
        docMeta.affiliations = document.getAffiliationList();
        docMeta.keywords = document.getKeywordList();
        docMeta.tags = document.getTagList();
        docMeta.authors = document.getAuthorList();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        if (document.getAppendDate() != "") {
            docMeta.appendDate = simpleDateFormat.parse(document.getAppendDate());
        } else {
            docMeta.appendDate = new java.util.Date();
        }
        if (document.getCreateDate() != "") {
            docMeta.createDate = simpleDateFormat.parse(document.getCreateDate());
        } else {
            docMeta.createDate = new java.util.Date();
        }
        if (document.getUpdateDate() != "") {
            docMeta.updateDate = simpleDateFormat.parse(document.getUpdateDate());
        } else {
            docMeta.updateDate = new java.util.Date();
        }
        return docMeta;
    }

    /**
     * 创建Meta表
     * @return
     */
    public static boolean createMetaTable(Connection connection) throws SQLException {
        Statement statement = connection.createStatement();
//        String sql = "CREATE TABLE IF NOT EXISTS doc_meta ()";
//        String sql = "CREATE TABLE IF NOT EXISTS `doc_meta`(`internal_identifier` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,`title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,`version` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,`type` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,`keywords` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,`url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,`authors` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,`affiliations` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,`abstract_text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,`content_text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,`schema` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,`bounding` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,`tags` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,`comment` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,`catalog_identifier` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,`uuid` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,`parent_identifier` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,`append_date` datetime NULL DEFAULT NULL,`update_date` datetime NULL DEFAULT NULL,`create_date` datetime NULL DEFAULT NULL,`memo` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, PRIMARY KEY (`internal_identifier`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic";
        String sql = "CREATE TABLE IF NOT EXISTS doc_meta(internal_identifier varchar(255)  NOT NULL,title varchar(255)  NULL DEFAULT NULL,version varchar(10) NULL DEFAULT NULL,type varchar(10) NULL DEFAULT NULL,keywords varchar(255) NULL DEFAULT NULL,url varchar(255)  NULL DEFAULT NULL,authors varchar(255) NULL DEFAULT NULL,affiliations varchar(255) NULL DEFAULT NULL,abstract_text varchar(1024) NULL DEFAULT NULL,content_text varchar(1024) NULL DEFAULT NULL,schema varchar(1024) NULL DEFAULT NULL,bounding varchar(255) NULL DEFAULT NULL,tags varchar(255) NULL DEFAULT NULL,comment varchar(1024) NULL DEFAULT NULL,catalog_identifier varchar(255) NULL DEFAULT NULL,uuid varchar(255) NULL DEFAULT NULL,parent_identifier varchar(255) NULL DEFAULT NULL,append_date timestamp NULL DEFAULT NULL,update_date timestamp NULL DEFAULT NULL,create_date timestamp NULL DEFAULT NULL,memo varchar(1024)  NULL DEFAULT NULL,PRIMARY KEY (internal_identifier) );";
        statement.execute(sql);
        statement.close();
        return true;
    }

    /**
     * 删除Meta表
     * @return
     */
    public static boolean dropMetaTable(Connection connection) throws SQLException {
        Statement statement = connection.createStatement();
        String sql = "DROP TABLE IF EXISTS doc_meta";
        statement.execute(sql);
        statement.close();
        return true;
    }

    /**
     * 将元数据插入Meta表
     * @param connection
     * @param meta
     * @return
     */
    public static boolean insert(Connection connection, DocMeta meta) throws SQLException, InvalidProtocolBufferException {
        String sql = "INSERT INTO doc_meta VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, meta.getInternalIdentifier().toString());
        preparedStatement.setString(2, meta.getTitle());
        preparedStatement.setString(3, meta.getVersion());
        preparedStatement.setString(4, meta.getType());
        preparedStatement.setString(5, StringUtils.join(meta.getKeywords(), ","));//转回去就用Arrays.asList(str.split(","))
        preparedStatement.setString(6, meta.getUrl());
        preparedStatement.setString(7, StringUtils.join(meta.getAuthors(), ","));
        preparedStatement.setString(8, StringUtils.join(meta.getAffiliations(), ","));
        preparedStatement.setString(9, meta.abstractText);
        preparedStatement.setString(10, meta.content);
        preparedStatement.setString(11, meta.schema);
        if (meta.bounding != null) {
            String s = meta.bounding.toByteString().toStringUtf8();
            preparedStatement.setString(12, s);
        } else {
            preparedStatement.setString(12, null);
        }
        preparedStatement.setString(13, StringUtils.join(meta.tags, ","));
        preparedStatement.setString(14, meta.comment);
        preparedStatement.setString(15, Identifier.create(meta.catalogIdentifier).toString());
        preparedStatement.setString(16, meta.uuid);
        preparedStatement.setString(17, Identifier.create(meta.parentIdentifier).toString());
        preparedStatement.setTimestamp(18 , new Timestamp(meta.appendDate.getTime()));
        preparedStatement.setTimestamp(19 , new Timestamp(meta.updateDate.getTime()));
        preparedStatement.setTimestamp(20 , new Timestamp(meta.createDate.getTime()));
        preparedStatement.setString(21, meta.memo);
        preparedStatement.executeUpdate();
        preparedStatement.close();
        return true;
    }
    /**
     * 将元数据从Meta表中删除
     * @param connection
     * @param meta
     * @return
     */
    public static boolean delete(Connection connection, DocMeta meta) throws SQLException {
        String sql = "DELETE FROM doc_meta WHERE internal_identifier = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, meta.internalIdentifier.clone().toString());
        preparedStatement.executeUpdate();
        preparedStatement.close();
        return true;
    }
    /**
     * 将元数据更新到Meta表
     * @param connection
     * @param meta
     * @return
     */
    public static boolean update(Connection connection, DocMeta meta) throws SQLException {
        String sql = "UPDATE doc_meta SET title = ?, version = ?, type = ?, keywords = ?, url = ?, authors = ?, affiliations = ?, abstract_text = ?, content_text = ?, schema = ?, boundling = ?, tags = ?, comment = ?, catalog_idetifier = ?, uuid = ?, parent_identifier = ?, append_date = ?, update_date = ?, create_date = ?, memo = ? WHERE internal_identifier = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, meta.getTitle());
        preparedStatement.setString(2, meta.getVersion());
        preparedStatement.setString(3, meta.getType());
        preparedStatement.setString(4, StringUtils.join(meta.getKeywords(), ","));//转回去就用Arrays.asList(str.split(","))
        preparedStatement.setString(5, meta.getUrl());
        preparedStatement.setString(6, StringUtils.join(meta.getAuthors(), ","));
        preparedStatement.setString(7, StringUtils.join(meta.getAffiliations(), ","));
        preparedStatement.setString(8, meta.abstractText);
        preparedStatement.setString(9, meta.content);
        preparedStatement.setString(10, meta.schema);
        if (meta.bounding != null) {
            String s = meta.bounding.toByteString().toStringUtf8();
            preparedStatement.setString(11, s);
        } else {
            preparedStatement.setString(11, null);
        }
        preparedStatement.setString(12, StringUtils.join(meta.tags, ","));
        preparedStatement.setString(13, meta.comment);
        preparedStatement.setString(14, Identifier.create(meta.catalogIdentifier).toString());
        preparedStatement.setString(15, meta.uuid);
        preparedStatement.setString(16, Identifier.create(meta.parentIdentifier).toString());
        preparedStatement.setTimestamp(17 , new Timestamp(meta.appendDate.getTime()));
        preparedStatement.setTimestamp(18 , new Timestamp(meta.updateDate.getTime()));
        preparedStatement.setTimestamp(19 , new Timestamp(meta.createDate.getTime()));
        preparedStatement.setString(20, meta.memo);
        preparedStatement.setString(21, Identifier.create(meta.getInternalIdentifier()).toString());
        preparedStatement.executeUpdate();
        preparedStatement.close();
        return true;
    }

    /**
     * 根据文档内部ID查询对应的元数据
     * @param connection
     * @param identifier
     * @return
     */
    public static DocMeta selectByIdentifier(Connection connection, Identifier identifier) throws SQLException, ParseException, InvalidProtocolBufferException {
        String sql = "SELECT * FROM WHERE internal_identifier = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, Identifier.create(identifier).toString());
        ResultSet resultSet = preparedStatement.executeQuery();
        DocMeta docMeta = new DocMeta();
        while (resultSet.next()) {
            docMeta.setIdentifier(identifier);
            docMeta.title = resultSet.getString(2);
            docMeta.version = resultSet.getString(3);
            docMeta.type = resultSet.getString(4);
            docMeta.keywords = Arrays.asList(resultSet.getString(5).split(","));
            docMeta.url = resultSet.getString(6);
            docMeta.authors = Arrays.asList(resultSet.getString(7).split(","));
            docMeta.affiliations = Arrays.asList(resultSet.getString(8).split(","));
            docMeta.abstractText = resultSet.getString(9);
            docMeta.content = resultSet.getString(10);
            docMeta.schema = resultSet.getString(11);
            String s  = resultSet.getString(12);
            if (s != null) {
                docMeta.bounding=BoundingBox.parseFrom(ByteString.copyFromUtf8(s));
            }
            docMeta.tags = Arrays.asList(resultSet.getString(13).split(","));
            docMeta.comment = resultSet.getString(14);
            docMeta.catalogIdentifier = Identifier.fromString(resultSet.getString(15));
            docMeta.uuid = resultSet.getString(16);
            docMeta.parentIdentifier = Identifier.fromString(resultSet.getString(17));
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            docMeta.appendDate = sdf.parse(sdf.format(resultSet.getTimestamp(18)));
            docMeta.updateDate = sdf.parse(sdf.format(resultSet.getTimestamp(19)));
            docMeta.createDate = sdf.parse(sdf.format(resultSet.getTimestamp(20)));
            docMeta.memo = resultSet.getString(21);
        }
        resultSet.close();
        preparedStatement.close();
        return docMeta;
    }
    /**
     * 根据文档UUID查询对应的元数据
     * @param connection
     * @param uuid
     * @return
     */
    public static List<DocMeta> selectByUUID(Connection connection, String uuid) throws SQLException, ParseException, InvalidProtocolBufferException {
        String sql = "SELECT * FROM doc_meta WHERE uuid = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, uuid);
        ResultSet resultSet = preparedStatement.executeQuery();
        List<DocMeta> docMetas = new ArrayList<>();
        while (resultSet.next()) {
            DocMeta docMeta = new DocMeta();
            docMeta.setIdentifier(Identifier.fromString(resultSet.getString(1)));
            docMeta.title = resultSet.getString(2);
            docMeta.version = resultSet.getString(3);
            docMeta.type = resultSet.getString(4);
            docMeta.keywords = Arrays.asList(resultSet.getString(5).split(","));
            docMeta.url = resultSet.getString(6);
            docMeta.authors = Arrays.asList(resultSet.getString(7).split(","));
            docMeta.affiliations = Arrays.asList(resultSet.getString(8).split(","));
            docMeta.abstractText = resultSet.getString(9);
            docMeta.content = resultSet.getString(10);
            docMeta.schema = resultSet.getString(11);
            String s = resultSet.getString(12);
            if (s != null) {
                docMeta.bounding=BoundingBox.parseFrom(ByteString.copyFromUtf8(s));
            }
            docMeta.tags = Arrays.asList(resultSet.getString(13).split(","));
            docMeta.comment = resultSet.getString(14);
            docMeta.catalogIdentifier = Identifier.fromString(resultSet.getString(15));
            docMeta.uuid = resultSet.getString(16);
            docMeta.parentIdentifier = Identifier.fromString(resultSet.getString(17));
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            docMeta.appendDate = sdf.parse(sdf.format(resultSet.getTimestamp(18)));
            docMeta.updateDate = sdf.parse(sdf.format(resultSet.getTimestamp(19)));
            docMeta.createDate = sdf.parse(sdf.format(resultSet.getTimestamp(20)));
            docMeta.memo = resultSet.getString(21);
            docMetas.add(docMeta);
        }
        resultSet.close();
        preparedStatement.close();
        return docMetas;
    }
    /**
     * 根据文档标题查询对应的元数据
     * @param connection
     * @param title
     * @return
     */
    public static List<DocMeta> selectByTitle(Connection connection, String title) throws SQLException, ParseException, InvalidProtocolBufferException {
        String sql = "SELECT * FROM doc_meta WHERE title = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, title);
        ResultSet resultSet = preparedStatement.executeQuery();
        List<DocMeta> docMetas = new ArrayList<>();
        while (resultSet.next()) {
            DocMeta docMeta = new DocMeta();
            docMeta.setIdentifier(Identifier.fromString(resultSet.getString(1)));
            docMeta.title = resultSet.getString(2);
            docMeta.version = resultSet.getString(3);
            docMeta.type = resultSet.getString(4);
            docMeta.keywords = Arrays.asList(resultSet.getString(5).split(","));
            docMeta.url = resultSet.getString(6);
            docMeta.authors = Arrays.asList(resultSet.getString(7).split(","));
            docMeta.affiliations = Arrays.asList(resultSet.getString(8).split(","));
            docMeta.abstractText = resultSet.getString(9);
            docMeta.content = resultSet.getString(10);
            docMeta.schema = resultSet.getString(11);
            String s = resultSet.getString(12);
            if (s != null) {
                docMeta.bounding=BoundingBox.parseFrom(ByteString.copyFromUtf8(s));
            }
            docMeta.tags = Arrays.asList(resultSet.getString(13).split(","));
            docMeta.comment = resultSet.getString(14);
            docMeta.catalogIdentifier = Identifier.fromString(resultSet.getString(15));
            docMeta.uuid = resultSet.getString(16);
            docMeta.parentIdentifier = Identifier.fromString(resultSet.getString(17));
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            docMeta.appendDate = sdf.parse(sdf.format(resultSet.getTimestamp(18)));
            docMeta.updateDate = sdf.parse(sdf.format(resultSet.getTimestamp(19)));
            docMeta.createDate = sdf.parse(sdf.format(resultSet.getTimestamp(20)));
            docMeta.memo = resultSet.getString(21);
            docMetas.add(docMeta);
        }
        resultSet.close();
        preparedStatement.close();
        return docMetas;
    }
    /**
     * 根据分类目录编号查询对应的元数据
     * @param connection
     * @param catalogIdentifier
     * @return
     */
    public static List<DocMeta> selectByCatalogIdentifier(Connection connection, Identifier catalogIdentifier) throws SQLException, ParseException, InvalidProtocolBufferException {
        String sql = "SELECT * FROM doc_meta WHERE catalog_identifier = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, Long.toString(catalogIdentifier.longValue()));
        ResultSet resultSet = preparedStatement.executeQuery();
        List<DocMeta> docMetas = new ArrayList<>();
        while (resultSet.next()) {
            DocMeta docMeta = new DocMeta();
            docMeta.setIdentifier(Identifier.fromString(resultSet.getString(1)));
            docMeta.title = resultSet.getString(2);
            docMeta.version = resultSet.getString(3);
            docMeta.type = resultSet.getString(4);
            docMeta.keywords = Arrays.asList(resultSet.getString(5).split(","));
            docMeta.url = resultSet.getString(6);
            docMeta.authors = Arrays.asList(resultSet.getString(7).split(","));
            docMeta.affiliations = Arrays.asList(resultSet.getString(8).split(","));
            docMeta.abstractText = resultSet.getString(9);
            docMeta.content = resultSet.getString(10);
            docMeta.schema = resultSet.getString(11);
            String s = resultSet.getString(12);
            if (s != null) {
                docMeta.bounding=BoundingBox.parseFrom(ByteString.copyFromUtf8(s));
            }
            docMeta.tags = Arrays.asList(resultSet.getString(13).split(","));
            docMeta.comment = resultSet.getString(14);
            docMeta.catalogIdentifier = Identifier.fromString(resultSet.getString(15));
            docMeta.uuid = resultSet.getString(16);
            docMeta.parentIdentifier = Identifier.fromString(resultSet.getString(17));
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            docMeta.appendDate = sdf.parse(sdf.format(resultSet.getTimestamp(18)));
            docMeta.updateDate = sdf.parse(sdf.format(resultSet.getTimestamp(19)));
            docMeta.createDate = sdf.parse(sdf.format(resultSet.getTimestamp(20)));
            docMeta.memo = resultSet.getString(21);
            docMetas.add(docMeta);
        }
        resultSet.close();
        preparedStatement.close();
        return docMetas;
    }
    /**
     * 查询包含了所有标签的文档元数据
     * @param connection
     * @param tags
     * @return
     */
    public static List<DocMeta> selectByTags(Connection connection, List<String> tags) throws SQLException, ParseException, InvalidProtocolBufferException {
        int size = tags.size();
        String sql = "SELECT * FROM doc_meta WHERE 1 = 1";
        for (int i = 0; i < size; i++) {
            sql += " and tags like ?";
        }
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        for (int i = 1; i <= size; i++) {
            preparedStatement.setString(i, "%" + tags.get(i -1) + "%");
        }
        ResultSet resultSet = preparedStatement.executeQuery();
        List<DocMeta> docMetas = new ArrayList<>();
        while (resultSet.next()) {
            DocMeta docMeta = new DocMeta();
            docMeta.setIdentifier(Identifier.fromString(resultSet.getString(1)));
            docMeta.title = resultSet.getString(2);
            docMeta.version = resultSet.getString(3);
            docMeta.type = resultSet.getString(4);
            docMeta.keywords = Arrays.asList(resultSet.getString(5).split(","));
            docMeta.url = resultSet.getString(6);
            docMeta.authors = Arrays.asList(resultSet.getString(7).split(","));
            docMeta.affiliations = Arrays.asList(resultSet.getString(8).split(","));
            docMeta.abstractText = resultSet.getString(9);
            docMeta.content = resultSet.getString(10);
            docMeta.schema = resultSet.getString(11);
            String s = resultSet.getString(12);
            if (s != null) {
                docMeta.bounding=BoundingBox.parseFrom(ByteString.copyFromUtf8(s));
            }
            docMeta.tags = Arrays.asList(resultSet.getString(13).split(","));
            docMeta.comment = resultSet.getString(14);
            docMeta.catalogIdentifier = Identifier.fromString(resultSet.getString(15));
            docMeta.uuid = resultSet.getString(16);
            docMeta.parentIdentifier = Identifier.fromString(resultSet.getString(17));
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            docMeta.appendDate = sdf.parse(sdf.format(resultSet.getTimestamp(18)));
            docMeta.updateDate = sdf.parse(sdf.format(resultSet.getTimestamp(19)));
            docMeta.createDate = sdf.parse(sdf.format(resultSet.getTimestamp(20)));
            docMeta.memo = resultSet.getString(21);
            docMetas.add(docMeta);
        }
        resultSet.close();
        preparedStatement.close();
        return docMetas;
    }

    /**
     * 查询包含了所有关键字的文档元数据
     * @param connection
     * @param keywords
     * @return
     */
    public static List<DocMeta> selectByKeywords(Connection connection, List<String> keywords) throws SQLException, ParseException, InvalidProtocolBufferException {
        int size = keywords.size();
        String sql = "SELECT * FROM doc_meta WHERE 1 = 1";
        for (int i = 0; i < size; i++) {
            sql += " and keywords like ?";
        }
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        for (int i = 1; i <= size; i++) {
            preparedStatement.setString(i, "%" + keywords.get(i -1) + "%");
        }
        ResultSet resultSet = preparedStatement.executeQuery();
        List<DocMeta> docMetas = new ArrayList<>();
        while (resultSet.next()) {
            DocMeta docMeta = new DocMeta();
            docMeta.setIdentifier(Identifier.fromString(resultSet.getString(1)));
            docMeta.title = resultSet.getString(2);
            docMeta.version = resultSet.getString(3);
            docMeta.type = resultSet.getString(4);
            docMeta.keywords = Arrays.asList(resultSet.getString(5).split(","));
            docMeta.url = resultSet.getString(6);
            docMeta.authors = Arrays.asList(resultSet.getString(7).split(","));
            docMeta.affiliations = Arrays.asList(resultSet.getString(8).split(","));
            docMeta.abstractText = resultSet.getString(9);
            docMeta.content = resultSet.getString(10);
            docMeta.schema = resultSet.getString(11);
            String s = resultSet.getString(12);
            if (s != null) {
                docMeta.bounding=BoundingBox.parseFrom(ByteString.copyFromUtf8(s));
            }
            docMeta.tags = Arrays.asList(resultSet.getString(13).split(","));
            docMeta.comment = resultSet.getString(14);
            docMeta.catalogIdentifier = Identifier.fromString(resultSet.getString(15));
            docMeta.uuid = resultSet.getString(16);
            docMeta.parentIdentifier = Identifier.fromString(resultSet.getString(17));
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            docMeta.appendDate = sdf.parse(sdf.format(resultSet.getTimestamp(18)));
            docMeta.updateDate = sdf.parse(sdf.format(resultSet.getTimestamp(19)));
            docMeta.createDate = sdf.parse(sdf.format(resultSet.getTimestamp(20)));
            docMeta.memo = resultSet.getString(21);
            docMetas.add(docMeta);
        }
        resultSet.close();
        preparedStatement.close();
        return docMetas;
    }


    /**
     * 查询指定时间范围的文档元数据
     * @param connection
     * @param start
     * @param end
     * @return
     */
    public static List<DocMeta> selectByAppendDate(Connection connection, java.util.Date start, java.util.Date end) throws SQLException, ParseException, InvalidProtocolBufferException {
        String sql = "SELECT * FROM doc_meta WHERE append_date BETWEEN ? AND ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setTimestamp(1, new Timestamp(start.getTime()));
        preparedStatement.setTimestamp(2, new Timestamp(end.getTime()));
        ResultSet resultSet = preparedStatement.executeQuery();
        List<DocMeta> docMetas = new ArrayList<>();
        while (resultSet.next()) {
            DocMeta docMeta = new DocMeta();
            docMeta.setIdentifier(Identifier.fromString(resultSet.getString(1)));
            docMeta.title = resultSet.getString(2);
            docMeta.version = resultSet.getString(3);
            docMeta.type = resultSet.getString(4);
            docMeta.keywords = Arrays.asList(resultSet.getString(5).split(","));
            docMeta.url = resultSet.getString(6);
            docMeta.authors = Arrays.asList(resultSet.getString(7).split(","));
            docMeta.affiliations = Arrays.asList(resultSet.getString(8).split(","));
            docMeta.abstractText = resultSet.getString(9);
            docMeta.content = resultSet.getString(10);
            docMeta.schema = resultSet.getString(11);
            String s = resultSet.getString(12);
            if (s != null) {
                docMeta.bounding=BoundingBox.parseFrom(ByteString.copyFromUtf8(s));
            }
            docMeta.tags = Arrays.asList(resultSet.getString(13).split(","));
            docMeta.comment = resultSet.getString(14);
            docMeta.catalogIdentifier = Identifier.fromString(resultSet.getString(15));
            docMeta.uuid = resultSet.getString(16);
            docMeta.parentIdentifier = Identifier.fromString(resultSet.getString(17));
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            docMeta.appendDate = sdf.parse(sdf.format(resultSet.getTimestamp(18)));
            docMeta.updateDate = sdf.parse(sdf.format(resultSet.getTimestamp(19)));
            docMeta.createDate = sdf.parse(sdf.format(resultSet.getTimestamp(20)));
            docMeta.memo = resultSet.getString(21);
            docMetas.add(docMeta);
        }
        resultSet.close();
        preparedStatement.close();
        return docMetas;
    }

    /**
     * 查询指定时间范围的文档元数据
     * @param connection
     * @param start
     * @param end
     * @return
     */
    public static List<DocMeta> selectByCreateDate(Connection connection, java.util.Date start, java.util.Date end) throws SQLException, ParseException, InvalidProtocolBufferException {
        String sql = "SELECT * FROM doc_meta WHERE create_date BETWEEN ? AND ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setTimestamp(1, new Timestamp(start.getTime()));
        preparedStatement.setTimestamp(2, new Timestamp(end.getTime()));
        ResultSet resultSet = preparedStatement.executeQuery();
        List<DocMeta> docMetas = new ArrayList<>();
        while (resultSet.next()) {
            DocMeta docMeta = new DocMeta();
            docMeta.setIdentifier(Identifier.fromString(resultSet.getString(1)));
            docMeta.title = resultSet.getString(2);
            docMeta.version = resultSet.getString(3);
            docMeta.type = resultSet.getString(4);
            docMeta.keywords = Arrays.asList(resultSet.getString(5).split(","));
            docMeta.url = resultSet.getString(6);
            docMeta.authors = Arrays.asList(resultSet.getString(7).split(","));
            docMeta.affiliations = Arrays.asList(resultSet.getString(8).split(","));
            docMeta.abstractText = resultSet.getString(9);
            docMeta.content = resultSet.getString(10);
            docMeta.schema = resultSet.getString(11);
            String s = resultSet.getString(12);
            if (s != null) {
                docMeta.bounding=BoundingBox.parseFrom(ByteString.copyFromUtf8(s));
            }
            docMeta.tags = Arrays.asList(resultSet.getString(13).split(","));
            docMeta.comment = resultSet.getString(14);
            docMeta.catalogIdentifier = Identifier.fromString(resultSet.getString(15));
            docMeta.uuid = resultSet.getString(16);
            docMeta.parentIdentifier = Identifier.fromString(resultSet.getString(17));
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            docMeta.appendDate = sdf.parse(sdf.format(resultSet.getTimestamp(18)));
            docMeta.updateDate = sdf.parse(sdf.format(resultSet.getTimestamp(19)));
            docMeta.createDate = sdf.parse(sdf.format(resultSet.getTimestamp(20)));
            docMeta.memo = resultSet.getString(21);
            docMetas.add(docMeta);
        }
        resultSet.close();
        preparedStatement.close();
        return docMetas;
    }

    /**
     * 查询指定时间范围的文档元数据
     * @param connection
     * @param start
     * @param end
     * @return
     */
    public static List<DocMeta> selectByUpdateDate(Connection connection, java.util.Date start, java.util.Date end) throws SQLException, ParseException, InvalidProtocolBufferException {
        String sql = "SELECT * FROM doc_meta WHERE update_date BETWEEN ? AND ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setTimestamp(1, new Timestamp(start.getTime()));
        preparedStatement.setTimestamp(2, new Timestamp(end.getTime()));
        ResultSet resultSet = preparedStatement.executeQuery();
        List<DocMeta> docMetas = new ArrayList<>();
        while (resultSet.next()) {
            DocMeta docMeta = new DocMeta();
            docMeta.setIdentifier(Identifier.fromString(resultSet.getString(1)));
            docMeta.title = resultSet.getString(2);
            docMeta.version = resultSet.getString(3);
            docMeta.type = resultSet.getString(4);
            docMeta.keywords = Arrays.asList(resultSet.getString(5).split(","));
            docMeta.url = resultSet.getString(6);
            docMeta.authors = Arrays.asList(resultSet.getString(7).split(","));
            docMeta.affiliations = Arrays.asList(resultSet.getString(8).split(","));
            docMeta.abstractText = resultSet.getString(9);
            docMeta.content = resultSet.getString(10);
            docMeta.schema = resultSet.getString(11);
            String s = resultSet.getString(12);
            if (s != null) {
                docMeta.bounding=BoundingBox.parseFrom(ByteString.copyFromUtf8(s));
            }
            docMeta.tags = Arrays.asList(resultSet.getString(13).split(","));
            docMeta.comment = resultSet.getString(14);
            docMeta.catalogIdentifier = Identifier.fromString(resultSet.getString(15));
            docMeta.uuid = resultSet.getString(16);
            docMeta.parentIdentifier = Identifier.fromString(resultSet.getString(17));
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            docMeta.appendDate = sdf.parse(sdf.format(resultSet.getTimestamp(18)));
            docMeta.updateDate = sdf.parse(sdf.format(resultSet.getTimestamp(19)));
            docMeta.createDate = sdf.parse(sdf.format(resultSet.getTimestamp(20)));
            docMeta.memo = resultSet.getString(21);
            docMetas.add(docMeta);
        }
        resultSet.close();
        preparedStatement.close();
        return docMetas;
    }


    public void setIdentifier(Identifier identifier){
        this.internalIdentifier=Identifier.create();
        this.internalIdentifier.copyFrom(identifier);
    }

    public Identifier getInternalIdentifier() {
        return (Identifier) internalIdentifier.clone();
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public List<String> getKeywords() {
        return keywords;
    }

    public void setKeywords(List<String> keywords) {
        this.keywords = keywords;
    }

    public String getVersion() {
        return version;
    }

    public void setVersion(String version) {
        this.version = version;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public List<String> getAuthors() {
        return authors;
    }

    public void setAuthors(List<String> authors) {
        this.authors = authors;
    }

    public List<String> getAffiliations() {
        return affiliations;
    }

    public void setAffiliations(List<String> affiliations) {
        this.affiliations = affiliations;
    }

    public String getAbstractText() {
        return abstractText;
    }

    public void setAbstractText(String abstractText) {
        this.abstractText = abstractText;
    }

    public String getSchema() {
        return schema;
    }

    public void setSchema(String schema) {
        this.schema = schema;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }

    public BoundingBox getBounding() {
        return bounding;
    }

    public void setBounding(BoundingBox bounding) {
        this.bounding = bounding;
    }

    public List<String> getTags() {
        return tags;
    }

    public void setTags(List<String> tags) {
        this.tags = tags;
    }

    public Identifier getCatalogIdentifier() {
        return catalogIdentifier;
    }

    public void setCatalogIdentifier(Identifier catalogIdentifier) {
        this.catalogIdentifier = catalogIdentifier;
    }

    public String getUuid() {
        return uuid;
    }

    public void setUuid(String uuid) {
        this.uuid = uuid;
    }

    public Identifier getParentIdentifier() {
        return parentIdentifier;
    }

    public void setParentIdentifier(Identifier parentIdentifier) {
        this.parentIdentifier = parentIdentifier;
    }




    public String getComment() {
        return comment;
    }

    public void setComment(String comment) {
        this.comment = comment;
    }

    public java.util.Date getAppendDate() {
        return appendDate;
    }

    public void setAppendDate(java.util.Date appendDate) {
        this.appendDate = appendDate;
    }

    public java.util.Date getUpdateDate() {
        return updateDate;
    }

    public void setUpdateDate(java.util.Date updateDate) {
        this.updateDate = updateDate;
    }

    public java.util.Date getCreateDate() {
        return createDate;
    }

    public void setCreateDate(java.util.Date createDate) {
        this.createDate = createDate;
    }

    public String getMemo() {
        return memo;
    }

    public void setMemo(String memo) {
        this.memo = memo;
    }

    @Override
    public Object clone() {
        return null;
    }

    @Override
    public boolean load(DataInput dataInput) throws IOException {
        return false;
    }

    @Override
    public boolean store(DataOutput dataOutput) throws IOException {
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        ObjectOutputStream objectOutputStream = new ObjectOutputStream(byteArrayOutputStream);
        objectOutputStream.writeObject(this);
        byte[] bytes = byteArrayOutputStream.toByteArray();
        dataOutput.write(bytes);
        return true;
    }
}
